package sdu.acs.services;

import java.sql.Connection;
import java.sql.ResultSet;

import com.mysql.jdbc.PreparedStatement;

public class DBService {
	public static Integer getStudentIdByCard(Connection conn, long card) {
		try {
			String sql = "SELECT id FROM student WHERE card = ?";
			PreparedStatement statement = (PreparedStatement) conn
					.prepareStatement(sql);

			statement.setLong(1, card);

			ResultSet result = statement.executeQuery();
			if (result.first()) {
				return result.getInt("id");
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String getSubjectId(Connection conn,int room, int week, String time) {
		try {
			String sql = "SELECT subject FROM shedule WHERE classroom = ? AND week = ? AND start < ? AND end > ?";
			PreparedStatement statement = (PreparedStatement) conn
					.prepareStatement(sql);

			statement.setInt(1, room);
			statement.setInt(2, week);
			statement.setString(3, time);
			statement.setString(4, time);

			ResultSet result = statement.executeQuery();
			if (result.first()) {
				return result.getString("subject");
			}
			return null;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public static boolean insertAttandance(Connection conn, int studentID,
			String subjectID, String date, String time) {
		try {
			String sql = "INSERT INTO attendance (`id`, `student`, `subject`, `date`, `time`) "
					+ "VALUES (NULL, ?, ?, ?, ?);";
			PreparedStatement statement = (PreparedStatement) conn
					.prepareStatement(sql);

			statement.setInt(1, studentID);
			statement.setString(2, subjectID);
			statement.setString(3, date);
			statement.setString(4, time);
			
			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
}
